﻿using Oracle.ManagedDataAccess.Client;  // 引入 Oracle 数据库访问的命名空间
using System;  // 引入基础类库命名空间
using System.Data;  // 引入数据处理的命名空间

namespace Bank_database_system  // 定义命名空间 Bank_database_system
{
    public class Withdrawal_Operations  // 定义类 Withdrawal_Operations
    {
        // 定义数据库连接字符串作为常量
        private const string connectionString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=47.96.39.153)(PORT=1521))(CONNECT_DATA=(SID=orcl)));
                                                 User Id=system;
                                                 Password=Tongji123456;
                                                 Connection Timeout=5;";

        public static string Withdrawal(
        string withdrawalCreditCardNumbers,
        decimal withdrawalAmount,
        string withdrawalBranchid,
        string withdrawalCurrencyKind,
        string ip,
        string withdrawalStaffId)
        {
            string result = null;

            // 使用 OracleConnection 对象建立数据库连接
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    // 打开数据库连接
                    connection.Open();

                    // 定义调用存储函数的 SQL 语句
                    string functionCall = "WITHDRAWAL";

                    // 使用 OracleCommand 对象调用存储函数
                    using (OracleCommand command = new OracleCommand(functionCall, connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        // 修改返回值参数类型为 OracleDbType.Varchar2 并设置合适的大小
                        OracleParameter returnValue = new OracleParameter("return_value", OracleDbType.Varchar2, 2000)
                        {
                            Direction = ParameterDirection.ReturnValue
                        };
                        command.Parameters.Add(returnValue);

                        // 添加参数并设置其值
                        command.Parameters.Add("WITHDRAWAL_CREDIT_CARD_NUMBERS", OracleDbType.Varchar2).Value = withdrawalCreditCardNumbers;
                        command.Parameters.Add("WITHDRAWAL_AMOUNT", OracleDbType.Decimal).Value = withdrawalAmount;
                        command.Parameters.Add("WITHDRAWAL_BRANCH_ID", OracleDbType.Varchar2).Value = withdrawalBranchid;
                        command.Parameters.Add("WITHDRAWAL_CURRENCY_KIND", OracleDbType.Varchar2).Value = withdrawalCurrencyKind;
                        command.Parameters.Add("IP", OracleDbType.Varchar2).Value = ip;
                        command.Parameters.Add("WITHDRAWAL_STAFF_ID", OracleDbType.Varchar2).Value = withdrawalStaffId;

                        // 执行存储函数
                        command.ExecuteNonQuery();

                        // 根据返回值判断是否成功
                        result = returnValue.Value.ToString();
                    }
                }
                catch (Exception ex)  // 捕获并处理可能发生的异常
                {
                    // 输出错误信息到控制台
                    Console.WriteLine("An error occurred: " + ex.Message);
                    MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            return result;  // 返回操作结果
        }
        //获取汇率
        public static decimal GetCurrencyRate(string withdrawalCurrencyKind)
        {
            decimal currencyRate = 0;

            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                conn.Open();

                string sql = "SELECT RATE FROM CURRENCY WHERE CURRENCY_KIND = :WITHDRAWAL_CURRENCY_KIND";

                using (OracleCommand cmd = new OracleCommand(sql, conn))
                {
                    cmd.Parameters.Add(new OracleParameter("WITHDRAWAL_CURRENCY_KIND", withdrawalCurrencyKind));

                    object result = cmd.ExecuteScalar();
                    if (result != null && result != DBNull.Value)
                    {
                        currencyRate = Convert.ToDecimal(result);
                    }
                }
            }
            return currencyRate;
        }
    }
}
